package com.young.cms.web;

import com.young.cms.model.Column;
import com.young.cms.model.Table;
import com.young.cms.service.ICmsDBService;
import com.young.common.core.excel.ExcelExport;
import com.young.common.util.DateUtil;
import com.young.common.util.Result;
import com.young.common.util.StringUtils;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.ResponseBody;

import javax.annotation.Resource;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.util.*;

/**
 * 数据库操作
 * Created by Administrator on 2017/5/8.
 */
@Controller
@RequestMapping("/cms/db")
public class CmsDBOperController {

    @Resource(name = "cmsDBService")
    ICmsDBService cmsDBService;


    /**
     * 获取所有的表信息
     * @param request
     * @return
     * @throws Exception
     */
    @RequestMapping("/getTableList")
    @ResponseBody
    public Object getTableList(HttpServletRequest request) throws Exception{

        return new Result(cmsDBService.getTableListOfSimpleInfo());
    }

    /**
     * 查询某表的字段信息
     * @param tableName
     * @param request
     * @return
     * @throws Exception
     */
    @RequestMapping("/getColumsByTableName")
    @ResponseBody
    public Object getColumsByTableName(String tableName, HttpServletRequest request) throws Exception{

        return new Result(cmsDBService.getColumsByTableName(tableName));
    }

    /**
     * 下载数据库说明文档Excel
     * @param tableIds 待导出表名
     * @param all 是否导出全部(带平台表)
     * @param request
     * @param response
     * @throws Exception
     */
    @RequestMapping("/downloadDBExcel")
    public void downloadDBExcel(String tableIds, boolean all, HttpServletRequest request, HttpServletResponse response) throws Exception{
        String[] tables = null;
        List<Table> tableList = cmsDBService.getTableListOfSimpleInfo();//库中所有的表
        List<Table> checkTableList = new LinkedList<Table>();//待导出表
        if(StringUtils.isNotBlank(tableIds)){//当存在已选的表时,从所有的表中将被选中的挑出来
            //将已选表转为map形式,通过hash的方式降低之后筛选的复杂度
            Map<String, String> map = new HashMap<String, String>();
            String[] arr = tableIds.split(",");
            for (String one : arr){
                map.put(one, "1");
            }

            //遍历库中的所有表,将被选中的挑出来
            for (Table table : tableList){
                if (map.containsKey(table.getId())){//存在该表,则放入checkTableList
                    checkTableList.add(table);
                }
            }

        }else{//未选择固定的表,表示导出所有
            checkTableList = tableList;
        }

        //遍历checkTableList,查询各个表的字段信息
        for (Table table : checkTableList){
            table.setColumnList(cmsDBService.getColumsByTableName(table.getId()));
        }

        if (!all){//排除平台表
            Iterator<Table> it = checkTableList.iterator();
            while(it.hasNext()){
                Table table = it.next();
                if (table.getId().toLowerCase().indexOf("tb_cms") == 0 || table.getId().toLowerCase().indexOf("tb_ums") == 0
                        || table.getId().toLowerCase().indexOf("tb_fs") == 0 || table.getId().toLowerCase().indexOf("tb_common") == 0
                        || table.getId().toLowerCase().indexOf("tb_platform") == 0){
                    it.remove();
                }
            }
        }

        //生成excel
        ExcelExport ee  = new ExcelExport();
        for (Table table : checkTableList){
            ee.insertRow(new String[]{table.getId()+"("+table.getName()+")"});
            ee.insertRow(new String[]{"字段", "类型", "长度", "非空", "注释"});
            for (Column col : table.getColumnList()){
                ee.insertRow(new Object[]{col.getField(), col.getProType(), col.getLength(), col.getNotNull(), col.getRemark()});
            }
            ee.insertRow(new String[]{});//插入空行
        }
        ee.write2Response(response, "数据字典_"+ DateUtil.getCurrentDay()+".xlsx");
    }
}
